﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ThanhTu.GNC.Media.KQXS.DataAccess.DO;
using Oracle.ManagedDataAccess.Client;
using System.Data;
using ThanhTu.GNC.Media.KQXS.Common;

namespace ThanhTu.GNC.Media.KQXS.DataAccess.DAL
{
    public class UsersDAL : BaseDAL
    {
        public UsersDAL() : base() { }
        public int SaveUser(UsersDO vUsersDO)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "USERS_PKG.SAVEUSER";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("IN_USERNAME", OracleDbType.Varchar2);
                oraParam.Value = vUsersDO.Username;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_PASSWORD", OracleDbType.Varchar2);
                oraParam.Value = vUsersDO.Pass;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_FULLNAME", OracleDbType.Varchar2);
                oraParam.Value = vUsersDO.Fullname;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.Int32);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                int result = ExecuteNoneQuery(oraComm);

                if (!Convert.IsDBNull(oraComm.Parameters["outreturn"]))
                { result = Convert.ToInt32(oraComm.Parameters["outreturn"].Value.ToString()); }

                oraComm.Dispose();
                oraParam = null;

                return result;
            }
        }
        public int login(string username, string password)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "USERS_PKG.CHECKLOGIN";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("IN_USERNAME", OracleDbType.Varchar2);
                oraParam.Value = username;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_PASSWORD", OracleDbType.Varchar2);
                oraParam.Value = password;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.Int32);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);
                int result = ExecuteNoneQuery(oraComm);

                if (!Convert.IsDBNull(oraComm.Parameters["outreturn"]))
                { result = Convert.ToInt32(oraComm.Parameters["outreturn"].Value.ToString()); }

                oraComm.Dispose();
                oraParam = null;

                return result;
            }
        }
        public UsersDO GetByUser(string username, string password)
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "USERS_PKG.GETBYUSER";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("IN_USERNAME", OracleDbType.Varchar2);
                oraParam.Value = username;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("IN_PASSWORD", OracleDbType.Varchar2);
                oraParam.Value = password;
                oraComm.Parameters.Add(oraParam);

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);

                using (DataSet ds = GetDataSet(oraComm))
                {
                    DataRow dr = null;
                    UsersDO vUsersDO = null;

                    if (ds != null)
                    {
                        if (ds.Tables.Count > 0)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                dr = ds.Tables[0].Rows[0];
                                vUsersDO = new UsersDO();

                                if (!Convert.IsDBNull(dr["USERNAME"]))
                                { vUsersDO.Username = Convert.ToString(dr["USERNAME"]); }

                                if (!Convert.IsDBNull(dr["PASS"]))
                                { vUsersDO.Pass = Convert.ToString(dr["PASS"]); }

                                if (!Convert.IsDBNull(dr["FULLNAME"]))
                                { vUsersDO.Fullname = Utils.ToUTF8String((byte[])dr["FULLNAME"]); }

                                dr = null;
                            }
                        }
                        ds.Dispose();
                    }
                    oraComm.Dispose();
                    oraParam = null;
                    return vUsersDO;
                }
            }
        }
        public List<UsersDO> SelectAllUsers()
        {
            using (OracleCommand oraComm = new OracleCommand())
            {
                oraComm.CommandType = CommandType.StoredProcedure;
                oraComm.CommandText = "USERS_PKG.GETALLUSER";
                OracleParameter oraParam = null;

                oraParam = new OracleParameter("OUTRETURN", OracleDbType.RefCursor);
                oraParam.Direction = ParameterDirection.Output;
                oraComm.Parameters.Add(oraParam);
                using (DataSet ds = GetDataSet(oraComm))
                {
                    DataTable dt = null;
                    List<UsersDO> vUsersDOCollection = null;
                    DataRow dr = null;
                    UsersDO vUsersDO = null;

                    if (ds != null)
                    {
                        if (ds.Tables.Count > 0)
                        {
                            dt = ds.Tables[0];
                            vUsersDOCollection = new List<UsersDO>();

                            for (int i = 0; i < dt.Rows.Count; i++)
                            {
                                dr = dt.Rows[i];
                                vUsersDO = new UsersDO();

                                if (!Convert.IsDBNull(dr["USERNAME"]))
                                { vUsersDO.Username = Convert.ToString(dr["USERNAME"]); }

                                if (!Convert.IsDBNull(dr["PASS"]))
                                { vUsersDO.Pass = Convert.ToString(dr["PASS"]); }

                                if (!Convert.IsDBNull(dr["FULLNAME"]))
                                { vUsersDO.Fullname = Utils.ToUTF8String((byte[])dr["FULLNAME"]); }

                                vUsersDOCollection.Add(vUsersDO);
                                dr = null;
                                vUsersDO = null;
                            }

                            dt.Dispose();
                            dt = null;
                        }

                        ds.Dispose();
                    }
                    oraComm.Dispose();
                    oraParam = null;

                    return vUsersDOCollection;
                }
            }
        }
    }
}
